import pymysql
from xlsx_reader import read_to_dict, get_all_xlsx

# 1. 连接数据库，
conn = pymysql.connect(
    host='192.168.0.37',
    port=3306,
    user='base_db',
    password='ktWcmdPyTEJPsDj3',
    db='base_db',
    charset='utf8',
    # autocommit=True,    # 如果插入数据，， 是否自动提交? 和conn.commit()功能一致。
)
cur = conn.cursor()
filepath = 'D:\\send_to_song\\'
try:
    xlsxs = get_all_xlsx(filepath)
    for xlsx_path in xlsxs:
        for k, v in read_to_dict(xlsx_path).items():
            print(k, v.replace('#', '井'))
            # .replace("'", "\'")
            # .replace('"', '\"')
            # .replace(' ', '')
            # .replace('\t', ''))
            insert_sqli = '''INSERT INTO `exam` (`id`, `question`, `answer`) VALUES (null, "{}", "{}")'''.format(
                k, v.replace('"', '\"')
                    .replace("'", '\'')
            )
            cur.execute(insert_sqli)
except Exception as e:
    print("插入数据失败:", e)
else:
    # 如果是插入数据， 一定要提交数据， 不然数据库中找不到要插入的数据;
    conn.commit()
    print("插入数据成功;")
cur.close()
conn.close()
